let userModel = require('../model').userModel;
let questionArr = [1, 2, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 26, 28, 31, 32, 33, 34, 35, 39, 41, 42, 49, 52, 53, 54, 55, 56, 58, 59, 61, 62, 64, 67, 68, 69, 70, 71, 82, 84, 85, 88, 89, 90, 91, 99, 102, 104, 105, 108, 109, 112, 113, 115, 117, 119, 120, 123, 124, 125, 127, 135, 136, 139, 140, 142, 145];
let DaoTool = require('../lib/daoTool');
//查询基本用户信息
exports.queryUser = function (conn, params) {
    return new Promise(function (resolve, reject) {
        console.log(params);
        let sql = "select * from user where openid = ?;";
        conn.query(sql, [params.openid], function (err, result) {
            printLog("=queryUser=", this.sql);
            if (err) reject(err);
            resolve(result)
        });
    });
};

exports.queryUserByUserId = function (conn, params) {
    return new Promise(function (resolve, reject) {
        let sql = "select * from user where UserId = ?;";
        conn.query(sql, [params.UserId], function (err, result) {
            printLog("=queryUserByUserId=", this.sql);
            if (err) reject(err);
            resolve(result)
        });
    });
};
//查询用户详细信息
exports.queryUserDetail = function (conn, params) {
    return new Promise(function (resolve, reject) {
        let sql = "select * from user as u LEFT JOIN my_option as myo on u.openid=myo.openid LEFT JOIN ta_option as tao on u.openid = tao.openid where u.openid = ?";
        conn.query(sql, [params.openid], function (err, result) {
            printLog("=queryUserDetail=", this.sql);
            if (err) reject(err);
            resolve(result)
        });
    });
};
//创建用户
exports.createUser = function (conn, params) {
    return new Promise(function (resolve, reject) {
        let sql = "insert into user set ?;";
        conn.query(sql, [DaoTool.generateModelObject(params, userModel, 'create')], function (err, result) {
            printLog("=createUser=", this.sql);
            if (err) reject(err);
            resolve(result)
        });
    });
};
//获取用户列表
exports.selectUser = function (conn, params, isCount) {
    console.log(params);
    return new Promise(function (resolve, reject) {
        let sql = " from user as u left join my_option as myo on myo.openid=u.openid left join ta_option as tao on tao.openid = u.openid LEFT JOIN friend_request AS frt ON frt.MeOpenId = ? AND frt.TaOpenId = u.openid where u.openid != ? AND u.openid NOT in(select BlackOpenId from black_list as bkl where bkl.openid = ?) AND u.openid NOT in(select FriendOpenid from friend as frd where frd.openid=?) and matchingBirthday('" + new Date(params.selfInfo["MyField147"]).Format() + "',myo.MyField147,'" + params.selfInfo["TaField147"] + "')=1 and  matchingHeight('" + params.selfInfo["MyField148"] + "',myo.MyField148,'" + params.selfInfo["TaField148"] + "')=1 and" +
            " matchingSex(myo.MyField0,'" + params.selfInfo["TaField0"] + "')=1 " + getSelectUserWhere(params);
        let selectSql = "select u.*,frt.AddId,myo.MyField147 as Birthday,myo.MyField148 as Height " + formatMatchingSqlStr(params.selfInfo) + sql + " order by ToMe desc,u.UserId desc" + DaoTool.getPageParamsSqlStr(params.params) + ";";
        let countSql = "select count(1) as num" + sql;
        console.log(selectSql);
        conn.query(selectSql, [params.params.openid, params.params.openid, params.params.openid, params.params.openid], function (err, selectResult) {
            printLog("=selectUser err=", err);
            printLog("=selectUser=", this.sql);
            if (err) reject(err);
            if (!isCount) resolve(selectResult);
            conn.query(countSql, [params.params.openid, params.params.openid, params.params.openid, params.params.openid], function (err, countResult) {
                printLog("=selectUser err=", err);
                printLog("=selectUser=", this.sql);
                if (err) reject(err);
                resolve({data: selectResult, count: countResult[0].num});
            });
        });
    });
};
//获取用户列表
exports.no_register_selectUser = function (conn, params, isCount) {
    console.log(params);
    return new Promise(function (resolve, reject) {
        let sql = " from user as u left join my_option as myo on myo.openid=u.openid left join ta_option as tao on tao.openid = u.openid where sexMatching(myo.MyField0,'" + params.Sex + "')=1 " + getnrSelectUserWhere(params);
        let selectSql = "select u.*,myo.MyField147 as Birthday,myo.MyField148 as Height " + sql + " order by Sincerity desc" + DaoTool.getPageParamsSqlStr(params) + ";";
        let countSql = "select count(1) as num" + sql;
        console.log(selectSql);
        conn.query(selectSql, [], function (err, selectResult) {
            printLog("=selectUser err=", err);
            printLog("=selectUser=", this.sql);
            if (err) reject(err);
            if (!isCount) resolve(selectResult);
            conn.query(countSql, [], function (err, countResult) {
                printLog("=selectUser err=", err);
                printLog("=selectUser=", this.sql);
                if (err) reject(err);
                resolve({data: selectResult, count: countResult[0].num});
            });
        });
    });
};
exports.selectUser = function (conn, params, isCount) {
    console.log(params);
    return new Promise(function (resolve, reject) {
        let sql = " from user as u left join my_option as myo on myo.openid=u.openid left join ta_option as tao on tao.openid = u.openid LEFT JOIN friend_request AS frt ON frt.MeOpenId = ? AND frt.TaOpenId = u.openid left join friend f on f.FriendOpenid=u.openid and f.openid=? where u.openid != ? AND u.openid NOT in(select BlackOpenId from black_list as bkl where bkl.openid = ?) " +
            (
                params.params.UserNo ? '' : (
                    "and birthdayMatching(myo.MyField147,'" + params.selfInfo["TaField147"] + "')=1 " +
                "and heightMatching(myo.MyField148,'" + params.selfInfo["TaField148"] + "')=1 " +
                "and sexMatching(myo.MyField0,'" + params.selfInfo["MyField0"] + "')=1 "
                )
            ) +
            "and u.CanSearch='T' " + getSelectUserWhere(params);
        let selectSql = "select u.*,frt.AddId,myo.MyField147 as Birthday,myo.MyField148 as Height,f.FriendId," + formatMatchingSqlStr(params.selfInfo) + sql + " order by ToMe desc,u.UserId desc" + DaoTool.getPageParamsSqlStr(params.params) + ";";
        let countSql = "select count(1) as num" + sql;
        console.log(selectSql);
        conn.query(selectSql, [params.params.openid, params.params.openid, params.params.openid, params.params.openid], function (err, selectResult) {
            printLog("=selectUser err=", err);
            printLog("=selectUser=", this.sql);
            if (err) reject(err);
            if (!isCount) resolve(selectResult);
            conn.query(countSql, [params.params.openid, params.params.openid, params.params.openid, params.params.openid], function (err, countResult) {
                printLog("=selectUser err=", err);
                printLog("=selectUser=", this.sql);
                if (err) reject(err);
                resolve({data: selectResult, count: countResult[0].num});
            });
        });
    });
};
// exports.selectUser = function (conn, params, isCount) {
//     console.log(params);
//     return new Promise(function (resolve, reject) {
//         let sql = " from user as u left join my_option as myo on myo.openid=u.openid left join ta_option as tao on tao.openid = u.openid LEFT JOIN friend_request AS frt ON frt.MeOpenId = ? AND frt.TaOpenId = u.openid where u.openid != ? AND u.openid NOT in(select BlackOpenId from black_list as bkl where bkl.openid = ?) AND u.openid NOT in(select FriendOpenid from friend as frd where frd.openid=?) and matchingBirthday('" + new Date(params.selfInfo["MyField147"]).Format() + "',myo.MyField147,'" + params.selfInfo["TaField147"] + "')=1 and  matchingHeight('" + params.selfInfo["MyField148"] + "',myo.MyField148,'" + params.selfInfo["TaField148"] + "')=1 and" +
//             " matchingSex(myo.MyField0,'" + params.selfInfo["TaField0"] + "')=1 " + getSelectUserWhere(params);
//         let selectSql = "select u.*,frt.AddId" + formatMatchingSqlStr(params.selfInfo) + sql + " order by ToMe desc,u.UserId desc" + DaoTool.getPageParamsSqlStr(params.params) + ";";
//         let countSql = "select count(1) as num" + sql;
//         console.log(selectSql);
//         conn.query(selectSql, [params.params.openid, params.params.openid, params.params.openid, params.params.openid], function (err, selectResult) {
//             printLog("=selectUser err=", err);
//             printLog("=selectUser=", this.sql);
//             if (err) reject(err);
//             if (!isCount) resolve(selectResult);
//             conn.query(countSql, [params.params.openid, params.params.openid, params.params.openid, params.params.openid], function (err, countResult) {
//                 printLog("=selectUser err=", err);
//                 printLog("=selectUser=", this.sql);
//                 if (err) reject(err);
//                 resolve({data: selectResult, count: countResult[0].num});
//             });
//         });
//     });
// };
exports.pcUserList = function (conn, params, isCount) {
    return new Promise(function (resolve, reject) {
        let sql = ' from user u left join user_rights as urs on urs.openid = u.openid left join my_option myo on myo.openid = u.openid '+ getPCSelectUserWhere(params);
        let selectSql = 'select u.*,urs.*,myo.MyField0 as Sex ' + sql + " order by UserId desc " + DaoTool.getPageParamsSqlStr(params);
        let countSql = 'select count(1) as num' + sql;
        conn.query(selectSql, [], function (err, selectResult) {
            printLog("=pcUserList err=", err);
            printLog("=pcUserList=", this.sql);
            if (err) reject(err);
            if (!isCount) resolve(selectResult);
            conn.query(countSql, [], function (err, countResult) {
                printLog("=pcUserList err=", err);
                printLog("=pcUserList=", this.sql);
                if (err) reject(err);
                resolve({data: selectResult, count: countResult[0].num});
            });
        });
    });
};
//更新
exports.updateUser = function (conn, params) {
    return new Promise(function (resolve, reject) {
        let updateParams = DaoTool.generateModelObject(params, userModel, 'update');
        if (JSON.stringify(updateParams) == '{}') {
            resolve();
        } else {
            conn.query('update user set ? where openid=?;', [updateParams, params.openid], function (err, result) {
                printLog("=updateUser=", this.sql);
                if (err) reject(err);
                resolve(result);
            });
        }
    });
};
//更新
exports.pc_updateUser = function (conn, params) {
    return new Promise(function (resolve, reject) {
        let updateParams = DaoTool.generateModelObject(params, userModel, 'update');
        if (JSON.stringify(updateParams) == '{}') {
            resolve();
        } else {
            conn.query('update user set ? where openid=?;', [updateParams, params.openid], function (err, result) {
                printLog("=updateUser=", this.sql);
                if (err) reject(err);
                resolve(result);
            });
        }
    });
};
//更新
exports.addPaidAddNum = function (conn, params) {
    return new Promise(function (resolve, reject) {
        conn.query('update user set PaidAddNum=PaidAddNum+? where openid=?;', [params.PaidAddNum, params.openid], function (err, result) {
            printLog("=addPaidAddNum=", this.sql);
            if (err) reject(err);
            resolve(result);
        });
    });
};
exports.addShareAddNum = function (conn, params) {
    return new Promise(function (resolve, reject) {
        conn.query('update user set ShareAddNum=ShareAddNum+1 where openid=?;', [params.openid], function (err, result) {
            printLog("=addShareAddNum=", this.sql);
            if (err) reject(err);
            resolve(result);
        });
    });
};
//更新所有
exports.updateAllUser = function (conn, params) {
    return new Promise(function (resolve, reject) {
        conn.query('update user set ?;', [DaoTool.generateModelObject(params, userModel, 'update')], function (err, result) {
            printLog("=updateAllUser=", this.sql);
            if (err) reject(err);
            resolve(result);
        });
    });
};
//获取用户总数
exports.getAllUserNumber = function (conn) {
    return new Promise(function (resolve, reject) {
        conn.query('select count(1) as num from user', function (err, countResult) {
            if (err) reject(err);
            resolve(countResult[0].num);
        })
    });
};
//获取资料完整度
exports.getIntegrity = function (conn, params) {
    return new Promise(function (resolve, reject) {
        conn.query('select ' + integritySql() + ' from  my_option as myo LEFT JOIN ta_option as tao on myo.openid= tao.openid where myo.openid=?', [params.openid], function (err, result) {
            printLog("=getIntegrity=", this.sql);
            if (err) reject(err);
            resolve(result);
        })
    });
};

function getSelectUserWhere(params) {
    let sql = "";
    if (params.params.SelectType) {
        if (params.params.SelectType == "TheCity") {
            if (params.params.SelectValue) {
                sql += ' and (u.UserNo = ' + mysqlHelper.escape(params.params.SelectValue) + ' or u.NickName like ' + mysqlHelper.escape('%' + params.params.SelectValue + '%') + ') and u.Province=' + params.selfInfo.Province + ' and u.City=' + params.selfInfo.City;
            } else {
                sql += ' and u.Province=' + params.selfInfo.Province + ' and u.City=' + params.selfInfo.City;
            }
        } else if (params.params.SelectType == "WholeCountry") {
            if (params.params.SelectValue) {
                sql += ' and (u.UserNo = ' + mysqlHelper.escape(params.params.SelectValue) + ' or u.NickName like ' + mysqlHelper.escape('%' + params.params.SelectValue + '%') + ') '
            }
        }
    } else {
        sql += params.params.UserNo ? " and u.UserNo = " + mysqlHelper.escape(params.params.UserNo) : "";
        sql += params.params.Province ? " and u.Province = " + params.params.Province : "";
        sql += (params.params.City || params.params.City===0)? " and u.City = " + params.params.City : "";
    }
    return sql;
}
function getnrSelectUserWhere(params) {
    let sql = "";
    if (params.SelectType) {
        if (params.SelectType == "WholeCountry") {
            if (params.SelectValue) {
                sql += ' and (u.UserNo = ' + mysqlHelper.escape(params.SelectValue) + ' or u.NickName like ' + mysqlHelper.escape('%' + params.SelectValue + '%') + ') '
            }
        }
    } else {
        sql += params.NickName ? " and u.NickName like " + mysqlHelper.escape('%' + params.NickName + '%') : "";
        sql += params.UserNo ? " and u.UserNo = " + mysqlHelper.escape(params.UserNo) : "";
        sql += params.Province ? " and u.Province = " + params.Province : "";
        sql += (params.City || params.City===0)? " and u.City = " + params.City : "";
    }
    return sql;

    // if (params.UserListParams)
    //     sql += ' and (u.UserNo like ' + mysqlHelper.escape('%' + params.UserListParams + '%')
    //         + ' or u.NickName like ' + mysqlHelper.escape('%' + params.UserListParams + '%') + ')';
    // return sql;
}
function getPCSelectUserWhere(params) {
    let sql = "";
    sql += params.NickName ? " and u.NickName like " + mysqlHelper.escape('%' + params.NickName + '%') : "";
    sql += params.UserNo ? " and u.UserNo = " + mysqlHelper.escape(params.UserNo) : "";
    sql += params.Province ? " and u.Province = " + params.Province : "";
    sql += params.City ? " and u.City = " + params.City : "";
    sql += params.Sex ? " and myo.MyField0 = " + params.Sex : "";
    if (params.UserListParams)
        sql += ' and (u.UserNo = ' + mysqlHelper.escape(params.UserListParams)
            + ' or u.NickName like ' + mysqlHelper.escape('%' + params.UserListParams + '%') + ')';
    if (sql != '')
        sql = ' where ' + sql.substring(4);
    return sql;
}

function formatMatchingSqlStr(params) {
    try {
        let myo_sql = "";
        let tao_sql = "";
        for (let k = 1; k <= 68; k++) {
            let i = config.questionArr[k];
            if (!params["MyField" + i] || !params["TaField" + i])
                continue;
            if (myo_sql) {
                myo_sql += "+";
                tao_sql += "+";
            }
            myo_sql += '(case when myo.MyField' + i + ' is null then 0 else (matching(myo.MyField' + i + ',"' + params["TaField" + i] + '")) end)';
            tao_sql += '(case when tao.TaField' + i + ' is null then 0 else (matching("' + params["MyField" + i] + '",tao.TaField' + i + ')) end)';
        }
        myo_sql = "((" + (myo_sql ? myo_sql : 0) + ")/68) as ToMe";
        tao_sql = "((" + (tao_sql ? tao_sql : 0) + ")/68) as ToTa ";
        return myo_sql + "," + tao_sql;
    } catch (err) {
        console.log(err);
    }
}

function integritySql() {
    let sql = "";
    for (let i = 0; i < 68; i++) {
        sql += '(case when myo.MyField' + config.questionArr[i] + ' is null or myo.MyField' + config.questionArr[i] + '="" then 0 else 0.5 end) + (case when tao.TaField' + config.questionArr[i] + ' is null or tao.TaField' + config.questionArr[i] + '="" then 0 else 0.5 end) +';
    }
    sql = "((" + sql + "0)/68) as Integrity";
    return sql;
}

